﻿using System.Data;
using DevelopTool.Bll.DbBlls;
using DevelopTool.Dal;
using DevelopTool.Model.DbModels;

namespace DevelopTool.My.Plugins.Bll
{
    public class ComSqlBll
    {
        /// <summary>
        /// 数据库操作类
        /// </summary>
        private OracleDbDal oracleDbDal;

        public ComSqlBll(DbInfo dbInfo)
        {
            oracleDbDal = new OracleDbDal(new OracleDbBll(dbInfo).GetConnectionString());
        }


        /// <summary>
        /// 查询所有Com_Sql表数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetComSqlData()
        {
            string getComSql_Sql = @"SELECT T.OWNER,T.TABLE_NAME FROM ALL_TAB_COLS T WHERE UPPER(T.TABLE_NAME) LIKE '%COM_SQL%' AND 
                                                 T.COLUMN_NAME IN('ID','NAME','MEMO','TYPE','MODUAL','ISVALID') GROUP BY T.TABLE_NAME,T.OWNER";

            DataTable comSqlTables = oracleDbDal.GetDataTable(getComSql_Sql);

            DataTable dataTable = new DataTable();

            foreach(DataRow dataRow in comSqlTables.Rows)
            {
                try
                {
                    string sql = $"SELECT T.ID,T.NAME,T.MEMO,T.TYPE,T.MODUAL,T.ISVALID FROM {dataRow["OWNER"]}.{dataRow["TABLE_NAME"]} T";
                    DataTable data = oracleDbDal.GetDataTable(sql);

                    //新增两个列：OWNER、TABLE_NAME
                    data.Columns.Add("OWNER",typeof(string));
                    data.Columns.Add("TABLE_NAME",typeof(string));
                    for(int i = 0 ; i < data.Rows.Count ; i++)
                    {
                        data.Rows[i]["OWNER"] = dataRow["OWNER"];
                        data.Rows[i]["TABLE_NAME"] = dataRow["TABLE_NAME"];
                        data.Rows[i]["ISVALID"] = data.Rows[i]["ISVALID"].ToString() == "1" ? "可用" : "不可用";
                    }

                    dataTable.Merge(data);
                }
                catch { continue; }

            }
            return dataTable;
        }

    }
}
